import pymysql


class Commondata:
    url = {"sit": {'user': "http://www.czyl888.com:3000/#/login/index",
                   'admin': 'https://testcreadmin.aligy.cn/'},
           "hotfix": {'user': "https://testcrehotfix.aligy.cn/login/",
                      'admin': "https://testcrehotfixadmin.aligy.cn/"},
           "BOLPRE": {'user': "https://prebol.crelpc.com/login/",
                      'admin': "https://adminprebol.crelpc.com/"},
           # "true":'https://console.cloud.trueidc.com/login'  idc项目的生产环境
           }

    register_url = {"sit": {"user": "https://testcre.aligy.cn/register?inviteId=@a",
                            "admin": "https://testcreadmin.aligy.cn/register?inviteId=@a"},
                    "hotfix": {'user': "https://testcrehotfix.aligy.cn/register?inviteId=@a",
                               'admin': "https://testcrehotfixadmin.aligy.cn/register?inviteId=@a"},
                    "BOLPRE": {'user': "https://prebol.crelpc.com/register?inviteId=@a",
                               'admin': "https://adminprebol.crelpc.com/register?inviteId=@a"},
                    'true': 'https://console.cloud.trueidc.com/register?inviteId=@a'}

    user_register_ur = {"sit": 'https://testcre.aligy.cn/register',
                        'hotfix': "https://testcrehotfix.aligy.cn/register",
                        'BOLPRE': "https://prebol.crelpc.com/register"}

    home_page = {"sit": {"user": "https://testcre.aligy.cn/work_bench/dashboard",
                         "admin": "https://testcreadmin.aligy.cn/console/userRegistApproval"},
                 "hotfix": {'user': "https://testcrehotfix.aligy.cn/work_bench/dashboard",
                            'admin': "https://testcrehotfixadmin.aligy.cn/console/userRegistApproval"},
                 "BOLPRE": {'user': "https://prebol.crelpc.com/work_bench/dashboard",
                            'admin': "https://adminprebol.crelpc.com/console/userRegistApproval"},
                 'true': 'https://console.cloud.trueidc.com/register?inviteId=@a'}

    hosts = {"sit": {"path": "/etc/hosts", "content": [
        '10.10.192.102 one.console.tbres.telkomsigma.co.id',
        '10.10.192.102 asc.tbres.telkomsigma.co.id',
        '10.10.192.102 one.cdn.tbres.telkomsigma.co.id',
        '10.10.192.102 ydconsole.tbres.telkomsigma.co.id',
        '10.10.192.102 cs.console.tbres.telkomsigma.co.id',
        '10.10.192.102 rds.tbres.telkomsigma.co.id',
        '10.10.192.102 asd.m.id-jakarta-testbed-d01.tbres.telkomsigma.co.id',
        '10.10.192.102 asrbr-portal.tbres.telkomsigma.co.id',
        '10.10.192.102 dbs.console.tbres.telkomsigma.co.id',
        '10.10.192.102 dts.console.tbres.telkomsigma.co.id',
        '10.10.192.102 account.tbres.telkomsigma.co.id',
        '10.10.192.102 ascdn.console.tbres.telkomsigma.co.id',
        '10.10.192.102 webvnc.tbres.telkomsigma.co.id',
        '103.147.5.167 cre.tbres.telkomsigma.co.id creadmin.tbres.telkomsigma.co.id'
    ]},
             "hostfix": {"path": "", "content": []},
             "BOLPRE": {"path": "/etc/hosts", "content": [
                 '192.168.193.118 http://www.saas.crelpc.com',
                 '192.168.193.118 admin.saas.crelpc.com',
                 '192.168.193.118 prebol.crelpc.com',
                 '192.168.193.118 adminprebol.crelpc.com',
                 '192.168.193.46 asc.tbres.telkomsigma.co.id one.console.tbres.telkomsigma.co.id one.cdn.tbres.telkomsigma.co.id ydconsole.tbres.telkomsigma.co.id ascdn.console.tbres.telkomsigma.co.id cs.console.tbres.telkomsigma.co.id rds.tbres.telkomsigma.co.id public.asapi.id-jakarta-testbed-d01.tbres.telkomsigma.co.id secspy-share.id-jakarta-testbed-d01.tbres.telkomsigma.co.id']}}

    download_file_path = "\\home\\seluser\\testproject\\Case\\python_ui\\Download"

    download_file_path_local = 'D:\\CloudTest-CRE_ui-20231016151756\\Case\\python_ui\\Download'

    upload_file_path = '/home/seluser/testproject/Case/python_ui/Test_data/tupian.svg'

    upload_file_path_local = "D:\\CloudTest-CRE_ui-20231016151756\\Case\\python_ui\\Test_data\\tupian.svg"

    # 数据库连接信息
    db_config = {"sit": {
        "host": "www.czyl888.com",
        "user": "root",
        # "password": "Stc@1019temp",
        "password": "!QAZ@WSX3edc",
        "database": ["bnzy_baojia"],
        "port": 3306,
        "maxconnections": [60],  # 最大连接数
        "mincached": 5,  # 最小空闲连接数
        # "maxcached" :20,  # 最大空闲连接数
        "blocking": True,
    },
        # "true":{
        #            "host": "45.252.53.86",
        #             "user": "ture_readonly",
        #             #"password": "Stc@1019temp",
        #             "password": "True!$Read0Nly",
        #             "database": ["cre"],
        #             "port": 23306,
        #             "maxconnections": [30],  # 最大连接数
        #             "mincached": 5,  # 最小空闲连接数
        #             # "maxcached" :20,  # 最大空闲连接数
        #             "blocking": True,
        #             },
        "hotfix": {
            "host": "10.10.168.23",
            "user": "root",
            # "password": "Stc@1019temp",
            "password": "Stc@1019temp",
            "database": ["cre-sigma-test"],
            "port": 31102,
            "maxconnections": [60],  # 最大连接数
            "mincached": 5,  # 最小空闲连接数
            # "maxcached" :20,  # 最大空闲连接数
            "blocking": True,
        },
        "BOLPRE": {
            "host": "192.168.193.45",
            "user": "root",
            # "password": "Stc@1019temp",
            "password": "1jian8Shu!",
            "database": ["pre_bol_cre"],
            "port": 3307,
            "maxconnections": [60],  # 最大连接数
            "mincached": 5,  # 最小空闲连接数
            # "maxcached" :20,  # 最大空闲连接数
            "blocking": True,
        },

    }

    db_sit = {
        "host": "10.10.220.124",
        "user": "root",
        # "password": "Stc@1019temp",
        "password": "cre@mysql8.0",
        "database": ["cre-sigma-test-s3"],
        "port": 3306,
        "maxconnections": [60],  # 最大连接数
        "mincached": 5,  # 最小空闲连接数
        # "maxcached" :20,  # 最大空闲连接数
        "blocking": True,
    }

    # 查询user_validation_code表
    query_user_validation_code_sql = "select validation_code from user_validation_code where send_target='@a' order by create_time desc limit 1;"

    # 查询用户的手机号
    query_user_phone = 'select phone_number from account_num_staff_profile where email="@a";'

    # 查询用户状态
    query_user_status = 'select state from account_num_staff_profile where email="@a";'

    # 查询组织
    query_org = 'select * from org where org_name="@a"'

    # 查询user_invite_id
    query_invite_id = 'SELECT id FROM user_invite_log WHERE email="@a";'

    # 查询user状态
    quert_user_status = "SELECT STATUS FROM `user_profile` WHERE email='@a' ORDER BY create_time DESC;"

    # 查询user_id
    query_user_id = "SELECT * FROM customer_profile WHERE customer_code =(SELECT customer_code FROM user_user_customer WHERE user_code = (SELECT user_code FROM user_profile WHERE email = '@a' AND user_domain = 'C' AND STATUS = '00'))"

    # 根据cust_id查询重置密码邮件
    query_reset_password = "SELECT content FROM @a WHERE cust_id=@b AND mail_to='@c' ORDER BY create_time DESC;"

    # 根据邮箱查询组织
    quer_org_name = "select org_name from org where code in (select org_code from org_user where user_code in (SELECT user_code FROM user_profile WHERE email='@a'));"

    # 查询user下面的usergroup
    query_user_group_name = "SELECT user_group_name FROM user_group WHERE user_group_id IN ( SELECT user_group_id FROM user_group_member WHERE user_code IN (SELECT user_code  FROM user_profile WHERE email='@a'))"

    # 查询user下面的role
    query_user_role = "select role_name from `auth_role` where role_code in (SELECT role_code FROM  `auth_user_role` WHERE user_code IN (SELECT user_code FROM user_profile WHERE email = '@a' AND user_domain = 'C' AND STATUS = '00'));"

    # 查询admin端user下面的role
    query_admin_user_role = "select role_name from `auth_role` where role_code in (SELECT role_code FROM  `auth_user_role` WHERE user_code IN (SELECT user_code FROM user_profile WHERE email = '@a' AND user_domain = 'S' AND STATUS = '00'));"

    # 查询usergroupname状态
    query_user_group_state = "SELECT user_group_state FROM user_group WHERE user_group_name='@a';"

    # 查询user的状态
    query_user_state = "SELECT STATUS FROM user_profile WHERE email = '@a';"

    # 查询绑定的usergroupname
    query_user_user_group_name = "SELECT user_group_name FROM `user_group` WHERE customer_code IN (SELECT customer_code FROM user_user_customer WHERE user_code IN (SELECT user_code FROM user_profile WHERE email = '@a' AND user_domain = 'C' AND STATUS = '00')) AND user_group_state='PUB';"

    # 查询绑定的usergroupname
    query_admin_user_group_name = "SELECT user_group_name FROM `user_group` WHERE customer_code IN (SELECT customer_code FROM user_user_customer WHERE user_code IN (SELECT user_code FROM user_profile WHERE email = '@a' AND user_domain = 'S' AND STATUS = '00')) AND user_group_state='PUB' ORDER BY CREATE_TIME DESC;"

    # 查询user_group下面的成员数量
    query_user_user_group_member = "SELECT user_group_member_id FROM user_group_member WHERE user_group_id IN (SELECT user_group_id FROM  user_group WHERE user_group_name='@a');"

    # 查询未注册的用户的状态
    query_uninvited_user_status = "SELECT status FROM user_invite_log WHERE  email = '@a';"

    # 根据order_id查询custid
    query_custid_by_order_id = 'SELECT partner_cust_id FROM order_main WHERE order_main_id ="@a";'

    # 根据账号修改create_limit的值
    update_creadit_limit = "UPDATE `account_credit_limit` SET available_credit_limit='@b' WHERE account_id IN (SELECT account_id FROM account_customer_relation WHERE customer_id IN(SELECT cust_id FROM `customer_profile`WHERE email='@a'));"

    # 根据账号查询账单金额
    query_gross_amount = "select gross_amount from `bill_statement` where cust_id in (SELECT cust_id FROM `customer_profile`WHERE email='@a');"

    # 根据账期查询pretax_gross_amount
    query_pretax_gross_amount = "select pretax_gross_amount from `bill_statement` where cust_id in (SELECT cust_id FROM `customer_profile`WHERE email='@a') and billing_cycle='@b' ;"

    # 查找最新创建的资源id
    query_instance_id = "select resource_id from instance_offer  where resource_id  like 'lb%' order by create_time desc limit 1"

    # 将job_infor表中包年包月延迟释放任务的下一次触发时间改为0
    update_next_trigger_time = "update job_info set trigger_next_time =0 where id=475"

    # 将job_info表中自动续费下一次触发时间改为0
    update_next_trigger_time_renew = "update job_info set trigger_next_time =0 where id=499"

    # 将对应产品的过期时间提前
    update_slb_expired_time = "update instance_offer set expire_time = '2023-02-28 10:34:39' where resource_id in (select t.resource_id from (select * from instance_offer order by create_time desc limit 1) as t)"
